package net.njcp.mybatis.frame.builder;

import net.njcp.mybatis.frame.constants.JoinType;
import net.njcp.mybatis.frame.utils.MapUtil;
import net.njcp.mybatis.frame.utils.MatchesUtil;
import net.njcp.mybatis.frame.utils.QueryUtil;
import org.apache.ibatis.jdbc.SQL;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Stream;

/**
 * @ClassName SelectSqlBuilder
 * @Description: 构造sql语句
 * @Author 柳拓
 * @Date 2019/11/5
 * @Version V1.0
 **/
public class CustomSqlBuilder {

    private SQL sql;

    private String tablePrefix;

    private Class tClass;

    private String tableName;

    private String aliasTableName;

    private List<String> customField;

    public CustomSqlBuilder(String tablePrefix){
        this.tablePrefix = tablePrefix;
        this.customField = new ArrayList<>();
    }

    public static CustomSqlBuilder build(){
        return build(null);
    }

    //初始化sql前缀,支持访问不同库下的表
    public static CustomSqlBuilder build(String tablePrefix){
        return new CustomSqlBuilder(tablePrefix);
    }

    /**
     * 查询指定表对象
     * @param tClass 表对象
     * @return
     */
    public CustomSqlBuilder select(Class tClass, String... columns){
        //初始化查询对象
        this.tClass = tClass;
        this.tableName = QueryUtil.getTableName(tClass);
        return select(tableName, columns);
    }

    public CustomSqlBuilder select(String tableName, String... columns){
        //只有查询语句才需要初始化sql
        this.sql = new SQL();
        this.tableName = tableName;
        this.aliasTableName = QueryUtil.getAliasTableName(tableName);
        //表名称构造
        StringBuilder tableBuilder = new StringBuilder();
        //如果数据表前缀不为空，加上前缀
        if (tablePrefix != null){
            tableBuilder.append(tablePrefix).append(".");
        }
        if (columns == null){
            sql.SELECT("*");
        }else{
            Stream.of(columns).forEach(column->{
                sql.SELECT(MatchesUtil.switchParam(column).trim());
            });

        }
        tableBuilder.append(tableName).append(" ").append(aliasTableName);
        sql.FROM(tableBuilder.toString());
        return this;
    }

    public CustomSqlBuilder selectMax(Class<?> tClass, String maxColumn ,String ... columns){
        //只有查询语句才需要初始化sql
        this.sql = new SQL();
        this.tableName = QueryUtil.getTableName(tClass);
        this.aliasTableName = QueryUtil.getAliasTableName(tableName);
        //表名称构造
        StringBuilder tableBuilder = new StringBuilder();
        tableBuilder.append(" Max(");
        if(tablePrefix != null){
            tableBuilder.append(tablePrefix).append(".");
            tableBuilder.append(MatchesUtil.switchParam(maxColumn).trim()).append(")").append(" as ").append(MatchesUtil.switchParam(maxColumn).trim());
            if(columns!=null) {
                Arrays.stream(columns).forEach(column -> tableBuilder.append(" , ").append(tablePrefix).append(".").append(MatchesUtil.switchParam(column).trim()));
            }
        }else {
            tableBuilder.append(MatchesUtil.switchParam(maxColumn).trim()).append(")").append(" as ").append(MatchesUtil.switchParam(maxColumn).trim());
            if(columns!=null) {
                Arrays.stream(columns).forEach(column -> tableBuilder.append(" , ").append(MatchesUtil.switchParam(column).trim()));
            }
        }

        sql.SELECT(tableBuilder.toString());
        sql.FROM(tableName+" "+aliasTableName);
        return this;
    }


    public CustomSqlBuilder selectMin(Class<?> tClass, String minColumn ,String ... columns){
        //只有查询语句才需要初始化sql
        this.sql = new SQL();
        this.tableName = QueryUtil.getTableName(tClass);
        this.aliasTableName = QueryUtil.getAliasTableName(tableName);
        //表名称构造
        StringBuilder tableBuilder = new StringBuilder();
        tableBuilder.append("Min(");
        if(tablePrefix != null){
            tableBuilder.append(tablePrefix).append(".");
            tableBuilder.append(MatchesUtil.switchParam(minColumn).trim()).append(")").append(" as ").append(MatchesUtil.switchParam(minColumn).trim());
            if(columns!=null) {
                Arrays.stream(columns).forEach(column -> tableBuilder.append(" , ").append(tablePrefix).append(".").append(MatchesUtil.switchParam(column).trim()));
            }
        }else {
            tableBuilder.append(MatchesUtil.switchParam(minColumn).trim()).append(")").append(" as ").append(MatchesUtil.switchParam(minColumn).trim());
            if(columns!=null) {
                Arrays.stream(columns).forEach(column -> tableBuilder.append(" , ").append(MatchesUtil.switchParam(column).trim()));
            }
        }

        sql.SELECT(tableBuilder.toString());
        sql.FROM(tableName+" "+aliasTableName);
        return this;
    }

    public CustomSqlBuilder select(Class tClass){
        return select(tClass, null);
    }

    public CustomSqlBuilder select(String tableName){
        return select(tableName, null);
    }

    /**
     * 删除指定表对象
     * @param tClass 表对象
     * @return
     */
    public CustomSqlBuilder delete(Class tClass){
        tableName = QueryUtil.getTableName(tClass);
        delete(tableName);
        return this;
    }

    public CustomSqlBuilder delete(String tableName){
        this.sql = new SQL(){{
            //表名称构造
            StringBuilder tableBuilder = new StringBuilder();
            //如果数据表前缀不为空，加上前缀
            if (tablePrefix != null){
                tableBuilder.append(tablePrefix).append(".");
            }
            tableBuilder.append(tableName);
            DELETE_FROM(tableBuilder.toString());
        }};
        return this;
    }

    public CustomSqlBuilder insert(String tableName){
        this.sql = new SQL(){{
            //表名称构造
            StringBuilder tableBuilder = new StringBuilder();
            //如果数据表前缀不为空，加上前缀
            if (tablePrefix != null){
                tableBuilder.append(tablePrefix).append(".");
            }
            tableBuilder.append(tableName);
            INSERT_INTO(tableBuilder.toString());
        }};
        return this;
    }

    /**
     * 自定义多值插入
     * @param map
     * @return
     */
    public CustomSqlBuilder values(Map<String, Object> map){
        map.entrySet().stream().filter(e -> e.getValue() != null).forEach(
                e -> {
                    String fieldName = MatchesUtil.switchParam(e.getKey());
                    String value = null;
                    if (e.getValue() instanceof String){
                        value = "'"+e.getValue()+"'";
                    }else if(e.getValue() instanceof Date){
                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        value = "'"+formatter.format(e.getValue())+"'";
                    }else{
                        value = String.valueOf(e.getValue());
                    }
                    sql.VALUES(fieldName, String.valueOf(value));
                }
        );
        return this;
    }

    public CustomSqlBuilder update(Class tClass){
        tableName = QueryUtil.getTableName(tClass);
        update(tableName);
        return this;
    }

    public CustomSqlBuilder update(String tableName){
        this.sql = new SQL(){{
            //表名称构造
            StringBuilder tableBuilder = new StringBuilder();
            //如果数据表前缀不为空，加上前缀
            if (tablePrefix != null){
                tableBuilder.append(tablePrefix).append(".");
            }
            tableBuilder.append(tableName);
            UPDATE(tableBuilder.toString());
        }};
        return this;
    }

    public CustomSqlBuilder set(Map<String, Object> map){
        map.entrySet().stream().filter(e -> e.getValue() != null).forEach(
                e -> {
                    String fieldName = MatchesUtil.switchParam(e.getKey());
                    String value;
                    if (e.getValue() instanceof Number){
                        value = String.valueOf(e.getValue());
                    }else if(e.getValue() instanceof Date){
                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        value = "'"+formatter.format(e.getValue())+"'";
                    }else{
                        value = "'"+e.getValue()+"'";
                    }
                    sql.SET(fieldName+"="+value);
                }
        );
        return this;
    }

    public CustomSqlBuilder set(Object o)  {
        Map<String,Object> map = new HashMap<>();
        try {
            map = MapUtil.Obj2Map(o);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return this.set(map);
    }

    public CustomSqlBuilder update(Object object){
        sql = UpdateSqlBuilder.buildUpdateSql(object);
        return this;
    }

    public CustomSqlBuilder count(String filed, String aliasName){
        customField.add(aliasName);
        StringBuilder countBuild = new StringBuilder();
        countBuild.append("count(").append(MatchesUtil.switchParam(filed)).append(") as ").append(aliasName);
        sql.SELECT(countBuild.toString());
        return this;
    }

    public CustomSqlBuilder sum(String filed, String aliasName){
        customField.add(aliasName);
        StringBuilder sumBuild = new StringBuilder();
        sumBuild.append("sum(").append(MatchesUtil.switchParam(filed)).append(") as ").append(aliasName);
        sql.SELECT(sumBuild.toString());
        return this;
    }

    public CustomSqlBuilder join(String tablePrefix, Class tClass, String condition){
        return joinTable(JoinType.JOIN, tablePrefix, tClass, condition);
    }

    public CustomSqlBuilder join(Class tClass, String condition){
        return joinTable(JoinType.JOIN, tablePrefix, tClass, condition);
    }

    public CustomSqlBuilder innerJoin(String tablePrefix, Class tClass, String condition){
        return joinTable(JoinType.INNER_JOIN, tablePrefix, tClass, condition);
    }

    public CustomSqlBuilder innerJoin(Class tClass, String condition){
        return joinTable(JoinType.INNER_JOIN, tClass, condition);
    }

    public CustomSqlBuilder outerJoin(String tablePrefix, Class tClass, String condition){
        return joinTable(JoinType.OUTER_JOIN, tablePrefix, tClass, condition);
    }

    public CustomSqlBuilder outerJoin(Class tClass, String condition){
        return joinTable(JoinType.OUTER_JOIN, tClass, condition);
    }

    public CustomSqlBuilder leftOuterJoin(String tablePrefix, Class tClass, String condition){
        return joinTable(JoinType.LEFT_OUTER_JOIN, tablePrefix, tClass, condition);
    }

    public CustomSqlBuilder leftOuterJoin(Class tClass, String condition){
        return joinTable(JoinType.LEFT_OUTER_JOIN, tClass, condition);
    }

    public CustomSqlBuilder rightOuterJoin(String tablePrefix, Class tClass, String condition){
        return joinTable(JoinType.RIGHT_OUTER_JOIN, tablePrefix, tClass, condition);
    }

    public CustomSqlBuilder rightOuterJoin(Class tClass, String condition){
        return joinTable(JoinType.RIGHT_OUTER_JOIN, tClass, condition);
    }

    public CustomSqlBuilder join(String tablePrefix, String joinTableName, String condition){
        return joinTable(JoinType.JOIN, tablePrefix, joinTableName, condition);
    }

    public CustomSqlBuilder join(String joinTableName, String condition){
        return joinTable(JoinType.JOIN, tablePrefix, joinTableName, condition);
    }

    public CustomSqlBuilder innerJoin(String tablePrefix, String joinTableName, String condition){
        return joinTable(JoinType.INNER_JOIN, tablePrefix, joinTableName, condition);
    }

    public CustomSqlBuilder innerJoin(String joinTableName, String condition){
        return joinTable(JoinType.INNER_JOIN, joinTableName, condition);
    }

    public CustomSqlBuilder outerJoin(String tablePrefix, String joinTableName, String condition){
        return joinTable(JoinType.OUTER_JOIN, tablePrefix, joinTableName, condition);
    }

    public CustomSqlBuilder outerJoin(String joinTableName, String condition){
        return joinTable(JoinType.OUTER_JOIN, joinTableName, condition);
    }

    public CustomSqlBuilder leftOuterJoin(String tablePrefix, String joinTableName, String condition){
        return joinTable(JoinType.LEFT_OUTER_JOIN, tablePrefix, joinTableName, condition);
    }

    public CustomSqlBuilder leftOuterJoin(String joinTableName, String condition){
        return joinTable(JoinType.LEFT_OUTER_JOIN, joinTableName, condition);
    }

    public CustomSqlBuilder rightOuterJoin(String tablePrefix, String joinTableName, String condition){
        return joinTable(JoinType.RIGHT_OUTER_JOIN, tablePrefix, joinTableName, condition);
    }

    public CustomSqlBuilder rightOuterJoin(String joinTableName, String condition){
        return joinTable(JoinType.RIGHT_OUTER_JOIN, joinTableName, condition);
    }

    /**
     * 指定库名的连接查询
     * @param joinType 连接类型
     * @param tablePrefix 库名
     * @param tClass 表对象
     * @param condition 连接条件
     * @return
     */
    public CustomSqlBuilder joinTable(JoinType joinType, String tablePrefix, Class tClass, String condition) {
        String joinTableName = QueryUtil.getTableName(tClass);
        return joinTable(joinType, tablePrefix, joinTableName, condition);
    }

    public CustomSqlBuilder joinTable(JoinType joinType, String tablePrefix, String joinTableName, String condition) {
        String aliasJoinTableName = QueryUtil.getAliasTableName(joinTableName);
        return joinTable(joinType,tablePrefix,tablePrefix,aliasJoinTableName,condition);
    }


    public CustomSqlBuilder joinTable(JoinType joinType, String tablePrefix, String joinTableName,String aliasJoinTableName, String condition){
        StringBuilder joinBuilder = new StringBuilder();
        if (tablePrefix != null){
            joinBuilder.append(tablePrefix).append(".");
        }else {
            if (this.tablePrefix != null){
                joinBuilder.append(this.tablePrefix).append(".");
            }
        }
        //构成连接的表名
        joinBuilder.append("( ").append(joinTableName).append(")").append(" ").append(aliasJoinTableName).append(" on ");
        //构成连接条件
        StringBuilder conditionBuilder = getConditionBuilder(aliasJoinTableName, aliasTableName, condition);
        joinBuilder.append(conditionBuilder.toString());
        switch (joinType){
            case JOIN:
                sql.JOIN(joinBuilder.toString());
                break;
            case INNER_JOIN:
                sql.INNER_JOIN(joinBuilder.toString());
                break;
            case OUTER_JOIN:
                sql.OUTER_JOIN(joinBuilder.toString());
                break;
            case LEFT_OUTER_JOIN:
                sql.LEFT_OUTER_JOIN(joinBuilder.toString());
                break;
            case RIGHT_OUTER_JOIN:
                sql.RIGHT_OUTER_JOIN(joinBuilder.toString());
                break;
            default:
                break;
        }
        return this;
    }

    /**
     * 未指定库名的连接查询：默认跟主表同库
     * @param joinType 连接类型
     * @param tClass 表对象
     * @param condition 连接条件
     * @return
     */
    public CustomSqlBuilder joinTable(JoinType joinType, Class tClass, String condition){
        return joinTable(joinType, null, tClass, condition);
    }

    public CustomSqlBuilder joinTable(JoinType joinType, String joinTableName, String condition){
        return joinTable(joinType, null, joinTableName, condition);
    }

    /**
     * 未指定了表的条件查询
     * @param condition 查询条件
     * @return
     */
    public CustomSqlBuilder where(String condition){
        return where(null, condition);
    }

    /**
     * 指定了表的条件查询
     * @param tClass 表对象
     * @param condition 查询条件
     * @return
     */
    public CustomSqlBuilder where(Class tClass, String condition){
        StringBuilder conditionBuilder = getConditionBuilder(tClass, condition);
        sql.WHERE(conditionBuilder.toString());
        return this;
    }

    /**
     * 后续查询条件
     * @param condition 查询条件
     * @return
     */
    public CustomSqlBuilder and(Class tClass, String condition){
        sql.AND();
        where(tClass, condition);
        return this;
    }

    public CustomSqlBuilder and(String condition){
        return and(null, condition);
    }

    /**
     * 后续查询条件
     * @param condition 查询条件
     * @return
     */
    public CustomSqlBuilder or(Class tClass, String condition){
        sql.OR();
        where(tClass, condition);
        return this;
    }

    public CustomSqlBuilder or(String condition){
        return or(null, condition);
    }

    /**
     * 指定表字段的分组查询
     * @param tClass 表对象
     * @param fields 字段
     * @return
     */
    public CustomSqlBuilder groupBy(Class tClass, String... fields){
        String aliasTableName;
        //如果未指定表,字段别名默认为主表的别名
        if (tClass != null){
            String tableName = QueryUtil.getTableName(tClass);
            aliasTableName = QueryUtil.getAliasTableName(tableName);
        }else {
            aliasTableName = this.aliasTableName;
        }
        StringBuilder groupByBuilder = new StringBuilder();
        Stream.of(fields).forEach(field->{
            groupByBuilder.append(aliasTableName).append(".").append(MatchesUtil.switchParam(field)).append(",");
        });
        sql.GROUP_BY(groupByBuilder.deleteCharAt(groupByBuilder.length()-1).toString());
        return this;
    }

    /**
     *
     * @param fields
     * @return
     */
    public CustomSqlBuilder groupBy(String... fields){
        return groupBy(null, fields);
    }

    /**
     * 指定了表的条件分组查询
     * @param tClass 表对象
     * @param condition 查询条件
     * @return
     */
    public CustomSqlBuilder having(Class tClass, String condition){
        StringBuilder conditionBuilder = getConditionBuilder(tClass, condition);
        sql.HAVING(conditionBuilder.toString());
        return this;
    }

    public CustomSqlBuilder having(String condition){
        return having(null, condition);
    }

    /**
     * 根据指定字段排序
     * @param tClass 表对象
     * @param fields 字段
     * @return
     */
    public CustomSqlBuilder orderBy(Class tClass, String... fields){
        String aliasTableName;
        //如果未指定表,字段别名默认为主表的别名
        if (tClass != null){
            String tableName = QueryUtil.getTableName(tClass);
            aliasTableName = QueryUtil.getAliasTableName(tableName);
        }else {
            aliasTableName = this.aliasTableName;
        }
        StringBuilder orderByBuilder = new StringBuilder();
        Arrays.asList(fields).stream().forEach(field->{
            orderByBuilder.append(aliasTableName).append(".").append(MatchesUtil.switchParam(field)).append(",");
        });
        sql.ORDER_BY(orderByBuilder.deleteCharAt(orderByBuilder.length()-1).toString());
        return this;
    }

    public CustomSqlBuilder orderBy(String... fields){
        return orderBy(null, fields);
    }

    public CustomSqlBuilder limit(int size){
        sql.LIMIT(size);
        return this;
    }

    public CustomSqlBuilder limit(int currPage, int pageSize){
        int limitSize = pageSize;
        int limitStart = (currPage-1)*pageSize;
        sql.LIMIT(limitStart+","+limitSize);
        return this;
    }

    public CustomSqlBuilder in(String field, CustomSqlBuilder customSqlBuilder){
        sql.WHERE(MatchesUtil.switchParam(field)+" in ("+customSqlBuilder.getSql().toString()+")");
        return this;
    }

    public CustomSqlBuilder notIn(String field, CustomSqlBuilder customSqlBuilder){
        sql.WHERE(MatchesUtil.switchParam(field)+" not in ("+customSqlBuilder.getSql().toString()+")");
        return this;
    }

    /**
     * 未指定表的查询条件
     * @param condition 查询条件
     * @return
     */
    public StringBuilder getConditionBuilder(String condition){
        return getConditionBuilder(null, condition);
    }

    /**
     * 指定表的查询条件
     * @param condition 查询条件
     * @return
     */
    public StringBuilder getConditionBuilder(Class tClass, String condition){
        String aliasTableName;
        //如果未指定表,字段别名默认为主表的别名
        if (tClass != null){
            String tableName = QueryUtil.getTableName(tClass);
            aliasTableName = QueryUtil.getAliasTableName(tableName);
        }else {
            aliasTableName = this.aliasTableName;
        }
        return getConditionBuilder(aliasTableName,null,condition);
    }

    /**
     * 连接查询条件
     * @param aliasJoinTableName 被连接表的别名
     * @param aliasTableName 连接对象表的别名
     * @param condition 连接条件 => 约束条件:被连接对象字段在左，连接对象在右
     * @return
     */
    public StringBuilder getConditionBuilder(String aliasJoinTableName, String aliasTableName, String condition){
        StringBuilder operatorBuilder = new StringBuilder();
        //筛选出运算符
        condition.chars().mapToObj(item -> (char) item).filter(c -> c.equals('=')||c.equals('>')||c.equals('<')).forEach(operatorBuilder::append);
        String operator = operatorBuilder.toString();
        if(operator == null || operator.equals("")){
            if(condition.indexOf(" in ") > -1){
                operator = " in ";
            }
            if(condition.indexOf(" is not ") > -1){
                operator = " is not ";
            }
        }
        String conditionArr[] = condition.split(operator);
        //属性
        String field = MatchesUtil.switchParam(conditionArr[0]).trim();

        String rightValue = conditionArr[1].trim();
        String value = null;
        //如果不包含字母
        if (!MatchesUtil.judgeContainsStr(rightValue) || rightValue.equals("null")) {
            value = rightValue;
            //判断是否为字符串
        }else if(rightValue.startsWith("'")&&rightValue.endsWith("'")){
            value =  rightValue;
        //判断是否为类的属性
        } else if (MatchesUtil.isClassOrPro(rightValue)){
            value = MatchesUtil.switchParam(conditionArr[1]).trim();
        //最后为字符串
        } else {
            value = "'"+rightValue+"'";
        }
        StringBuilder conditionBuilder = new StringBuilder();
        //如果连接表别名不为空
        if (aliasJoinTableName != null && customField.indexOf(field) < 0){
            conditionBuilder.append(aliasJoinTableName).append(".");
        }
        conditionBuilder.append(field).append(operator);
        //如果主表别名不为空
        if (aliasTableName != null){
            conditionBuilder.append(aliasTableName).append(".");
        }
        conditionBuilder.append(value).toString();
        return conditionBuilder;
    }

    public SQL getSql() {
        return sql;
    }

    public Class gettClass() {
        return tClass;
    }
}
